package com.atguigu.realtime.gmallpublisher.mapper;

import com.atguigu.realtime.gmallpublisher.bean.VisitorStats;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author lizhenchao@atguigu.cn
 * @Date 2021/3/27 10:29
 */
public interface VisitorStatsMapper {
    
    @Select("SELECT\n" +
        "    toHour(stt) AS hr,\n" +
        "    sum(if(is_new = '1', visitor_stats_2021.uv_ct, 0)) AS new_uv,\n" +
        "    sum(visitor_stats_2021.uv_ct) AS uv_ct,\n" +
        "    sum(visitor_stats_2021.pv_ct) AS pv_ct,\n" +
        "    sum(visitor_stats_2021.uj_ct) AS uj_ct\n" +
        "FROM visitor_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY toHour(stt)")
    List<VisitorStats> getVisitorStatsByHour(int date);
    
    
    @Select("SELECT\n" +
        "    is_new,\n" +
        "    sum(uv_ct) AS uv_ct,\n" +
        "    sum(pv_ct) AS pv_ct,\n" +
        "    sum(uj_ct) AS uj_ct\n" +
        "FROM visitor_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY is_new\n")
    List<VisitorStats> getVisitorStatsByNewFlat(int date);
}
